﻿using System;
using System.Data;
using MySql.Data.MySqlClient;


    public class DBMySql
    {
        private MySqlCommand _com;
        public MySqlCommand Comando
        {
            get { return _com; }
            set { _com = value; }
        }

        private MySqlConnection _con;
        public MySqlConnection Conexion
        {
            get { return _con; }
            set { _con = value; }
        }
        private string _server;
        public String Server
        {
            get { return _server; }
            set { _server = value; }
        }

        private string _database;
        public string Database
        {
            get { return _database; }
            set { _database = value; }
        }
        private string _user;
        public string User
        {
            get { return _user; }
            set { _user = value; }
        }
        private string _pwd;
        public string PWD
        {
            get { return _pwd; }
            set { _pwd = value; }
        }
        private DataTable _dt;
        public DataTable GetTabla
        {
            get { return _dt; }
            set { _dt = value; }
        }
        private MySqlDataReader _reader;

        public MySqlDataReader Reader
        {
            get { return _reader; }
            set { _reader = value; }
        }
        private static string _error;
        public static String Error
        {
            get { return _error; }
            set { _error = value; }
        }


        public DBMySql()
        {
            Conexion = new MySqlConnection("server = localhost;database = SRRA; Uid=root; Pwd= 123");
        }
        
        /// <summary>
        /// Abre la conexion con el servidor de mysql
        /// </summary>
        /// <returns>true o false</returns>
        public bool  Conectar()
        {
            try
            {
                if (Conexion.State == ConnectionState.Closed)
                {
                    Conexion.Open();
                }

                return true;
            }
            catch (Exception ex)
            {
                Error = ex.Message;
                return false;
            }
        }

        /// <summary>
        /// cierra la conexion con mysql
        /// </summary>
        /// <returns>true o false</returns>
        public  bool  Desconectar()
        {
            try
            {
                if (Conexion.State == ConnectionState.Open)
                {
                    Conexion.Close();
                }

                return true;
            }
            catch (Exception ex)
            {
                Error = ex.Message;
                return false;
            }
        }

        /// <summary>
        /// Ejecuta codigo sql
        /// </summary>
        /// <param name="comSql">SQL que se desea ejecutar</param>
        /// <returns>true o false</returns>
        public bool ExStoredProcedure(string nom, Parametros[] array)
        {
            try
            {
                Conectar();
                Comando = new MySqlCommand();
                Comando.CommandType = CommandType.StoredProcedure;
                Comando.CommandText = nom;
                Comando.Connection = Conexion;
                int x = 0;
                //Agregar parametros
                foreach (Parametros p in array) {
                    Comando.Parameters.AddWithValue(array[x].Nombre, array[x].Valor);
                    x++;
                }

                Comando.ExecuteNonQuery();
                Desconectar();
                return true;
            }
            catch (Exception ex)
            {
                Error = ex.Message;
                Desconectar();
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public bool  ReStoredProcedure(string nom)
        {
            try
            {
                Conectar();
                GetTabla = new DataTable();
                MySqlDataAdapter da = new MySqlDataAdapter(nom, Conexion);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.Fill(GetTabla);
                Desconectar();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public bool ReStoredProcedure(string nom,Parametros[] _array)
        {
            try
            {
                Conectar();
                GetTabla = new DataTable();
                MySqlDataAdapter da = new MySqlDataAdapter(nom, Conexion);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                int x = 0;
                //Agregar parametros
                foreach (Parametros p in _array)
                {
                    da.SelectCommand.Parameters.AddWithValue(_array[x].Nombre, _array[x].Valor);
                    x++;
                }
                da.Fill(GetTabla);
                Desconectar();
                return true;

            }
            catch (Exception ex)
            {
                return false;
            }
        }
    }